﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using HotelManager.Data_Transfer_Layer;

namespace HotelManager.Data_Access_Layer
{
    class khachhangDAO
    {
        #region Variables
        private OleDbConnection conn;
        private string error;
        #endregion

        #region Properties
        public string Error
        {
            get { return error; }
            set { error = value; }
        }
        #endregion

        public khachhangDAO()
        {
            error = "";
            try
            {
                string strConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\\hotel.accdb";
                conn = new OleDbConnection(strConn);
                conn.Open();
            }
            catch (Exception ex)
            {
                Error = "Không kết nối được đến cơ sở dữ liêu";
            }
        }

        public khachhangDTO[] get()
        {
            khachhangDTO[] objs;
            objs = new khachhangDTO[100];
            try
            {
                string sql = "SELECT * FROM khachhang";
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                int i = 0, n = dt.Rows.Count;
                objs = new khachhangDTO[n];
                while (reader.Read())
                {
                    objs[i] = new khachhangDTO();
                    objs[i].Kh_id = reader.GetInt32(0);
                    objs[i].Kh_hoten = reader.GetString(1).ToString();
                    objs[i].Kh_cmnd = reader.GetString(2).ToString();
                    objs[i].Kh_sodt = reader.GetString(3).ToString();
                    objs[i].Kh_gioitinh = reader.GetString(4).ToString();
                    objs[i].Kh_tongsongayo = reader.GetInt32(5);
                    objs[i].Kh_tongsotienphong = reader.GetInt32(6);
                    objs[i].Kh_tongsotiendv = reader.GetInt32(7);
                    objs[i].Kh_diemcong = reader.GetInt32(8);
                    objs[i].Kh_loaikh = reader.GetString(9).ToString();
                    i++;
                }
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            return objs;
        }

        public khachhangDTO View_Detail(int kh_id)
        {
            khachhangDTO obj = new khachhangDTO();
            try
            {
                string sql = "SELECT * FROM khachhang WHERE kh_id=" + kh_id;
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    obj.Kh_hoten = reader.GetString(1).ToString();
                    obj.Kh_cmnd = reader.GetString(2).ToString();
                    obj.Kh_sodt = reader.GetString(3).ToString();
                    obj.Kh_gioitinh = reader.GetString(4).ToString();
                    obj.Kh_tongsongayo = reader.GetInt32(5);
                    obj.Kh_tongsotienphong = reader.GetInt32(6);
                    obj.Kh_tongsotiendv = reader.GetInt32(7);
                    obj.Kh_diemcong = reader.GetInt32(8);
                    obj.Kh_loaikh = reader.GetString(9).ToString();
                }
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            return obj;
        }

        public khachhangDTO Find(string kh_ten)
        {
            khachhangDTO obj = new khachhangDTO();
            try
            {
                string sql = "SELECT * FROM khachhang WHERE kh_hoten='" + kh_ten + "'";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbDataReader reader1 = cmd.ExecuteReader();
                while (reader1.Read())
                {
                    obj.Kh_id = reader1.GetInt32(0);
                    obj.Kh_cmnd = reader1.GetString(2).ToString();
                    obj.Kh_sodt = reader1.GetString(3).ToString();
                    obj.Kh_gioitinh = reader1.GetString(4).ToString();
                    obj.Kh_tongsongayo = reader1.GetInt32(5);
                    obj.Kh_tongsotienphong = reader1.GetInt32(6);
                    obj.Kh_tongsotiendv = reader1.GetInt32(7);
                    obj.Kh_diemcong = reader1.GetInt32(8);
                    obj.Kh_loaikh = reader1.GetString(9).ToString();
                }
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
            return obj;
        }

        public void Delete(khachhangDTO obj)
        {
            try
            {
                string sql = "DELETE FROM khachhang WHERE kh_id=" + obj.Kh_id;
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }

        public void Insert(khachhangDTO obj)
        {
            try
            {
                string sql = "INSERT INTO khachhang(kh_hoten,kh_cmnd,kh_sodt,kh_gioitinh,kh_tongsongayo,kh_tongsotienphong,kh_tongsotiendv,kh_diemcong,kh_loaikh) VALUES('" + obj.Kh_hoten + "','" + obj.Kh_cmnd + "','" + obj.Kh_sodt + "','" + obj.Kh_gioitinh + "',0,0,0,0,'')";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }
        public void Update(khachhangDTO obj)
        {
            try
            {
                string sql1 = "UPDATE khachhang SET kh_hoten='" + obj.Kh_hoten + "',kh_cmnd='" + obj.Kh_cmnd + "',kh_sodt='" + obj.Kh_sodt + "',kh_gioitinh='" + obj.Kh_gioitinh + "' WHERE kh_id=" + obj.Kh_id;
                OleDbCommand cmd = new OleDbCommand(sql1, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Error = ex.ToString();
            }
        }
    }
}
